Database SQL – Date Function
การใช้งาน Date function จะมีด้วยกันหลายรูปแบบ ผู้ใช้งานสามารถเลือกใช้ function ที่เหมือนสมกับความต้องการได้ โดยส่วนมาก database แบบ RDBMS จะรองรับ function date ตามข้อมูลดังนี้
| Date Function | Expression | Detail |
| ADDDATE() | ADDDATE(expr,days) | เพิ่มจำนวนวัน |
| ADDTIME() | ADDTIME(expr1,expr2) | เพิ่มจำนวนเวลา |
| CONVERT_TZ() | CONVERT_TZ(dt,from_tz,to_tz) | เปลี่ยน timezone (GMT) |
| CURDATE() | CURDATE() | แสดงวันปัจจุบัน |
| CURRENT_DATE() | CURRENT_DATE() | แสดงวันปัจจุบัน |
| CURRENT_TIME() | CURRENT_TIME() | แสดงเวลาปัจจุบัน |
| CURRENT_TIMESTAMP() | CURRENT_TIMESTAMP() | แสดงวันและเวลาปัจจุบัน |
| CURTIME() | CURTIME() | แสดงเวลาปัจจุบัน |
| DATE_ADD() | DATE_ADD(date,INTERVAL expr unit) | เพิ่มจำนวนวัน |
| DATE_FORMAT() | DATE_FORMAT(date,format) | กำหนดรูปแบบการแสดงผลวันเวลา |
| DATE_SUB() | DATE_SUB(date,INTERVAL expr unit) | ลดจำนวนวัน |
| DATE() | DATE(expr) | แสดงเฉพาะส่วนของวันที่ |
| DATEDIFF() | DATEDIFF(expr1,expr2) | คำนวณระยะห่างของวัน |
| DAY() | DAY(date) | แสดงวันที่ของเดือน 1-31 |
| DAYNAME() | DAYNAME(date) | แสดงชื่อวันของสัปดาห์ |
| DAYOFMONTH() | DAYOFMONTH(date) | แสดงวันที่ของเดือน 1-31 |
| DAYOFWEEK() | DAYOFWEEK(date) | แสดงชื่อวันของสัปดาห์ |
| DAYOFYEAR() | DAYOFYEAR(date) | แสดงวันที่ของปี 1-366 |
| EXTRACT() | EXTRACT(unit FROM date) | แยกข้อมูลส่วนของวันเดือนปี |
| FROM_DAYS() | FROM_DAYS(N) | วันที่ห่างจาก Gregorian calendar (1582) |
| FROM_UNIXTIME() | FROM_UNIXTIME (unix_timestamp,format) | แสดงรูแบบวันที่แบบ Unix |
| HOUR() | HOUR(time) | แยกข้อมูลส่วนของชั่วโมง |
| LAST_DAY() | LAST_DAY(date) | แสดงวันสุดท้ายข้องเดือน (28,29,30,31) |
| LOCALTIME() | LOCALTIME() | แสดงเวลาปัจจุบัน |
| LOCALTIMESTAMP() | LOCALTIMESTAMP() | แสดงเวลาปัจจุบัน |
| MAKEDATE() | MAKEDATE(year,dayofyear) | ใช้ day of year มาสร้าง วันเดือนปี |
| MAKETIME() | MAKETIME(hour,minute,second) | สร้างเวลาจากตัวแปร ชั่วโมง,นาที,วินาที |
| MICROSECOND() | MICROSECOND(expr) | แสดงข้อมูลเฉพาะ microsecond |
| MINUTE() | MINUTE(time) | แสดงข้อมูลเฉพาะนาที |
| MONTH() | MONTH(date) | แสดงข้อมูลเฉพาะเดือน |
| MONTHNAME() | MONTHNAME(date) | แสดงข้อมูลชื่อเดือน |
| NOW() | NOW() | แสดงวันและเวลาปัจจุบัน |
| PERIOD_ADD() | PERIOD_ADD(P,N) | เพิ่มจำนวนเดือน (N) ใน (P) ด้วย format YYYYMM |
| PERIOD_DIFF() | PERIOD_DIFF(P1,P2) | คำนวณระยะห่างของเดือน |
| QUARTER() | QUARTER(date) | แสดงข้อมูล quarter (1-4) |
| SEC_TO_TIME() | SEC_TO_TIME(seconds) | เปลี่ยนวินาทีเป็น format เวลา HH:MM:S |
| SECOND() | SECOND(time) | แสดงข้อมูลเฉพาะวินาที |
| STR_TO_DATE() | STR_TO_DATE(str,format) | เปลี่ยน string เป็น date format |
| SUBDATE() | SUBDATE(expr,days) | ลดจำนวนวัน |
| SUBTIME() | SUBTIME(expr1,expr2) | ลดจำนวนเวลา |
| SYSDATE() | SYSDATE() | แสดงวันเวลาปัจจุบัน |
| TIME_FORMAT() | TIME_FORMAT(time,format) | แสดงเวลาตาม format ที่ระบุ |
| TIME_TO_SEC() | TIME_TO_SEC(time) | แปลงเวลาเป็นหน่วยวินาที (0-86400) |
| TIME() | TIME(expr) | แสดงเฉพาะส่วนของเวลา |
| TIMEDIFF() | TIMEDIFF(expr1,expr2) | คำนวนส่วนต่างของเวลา |
| TIMESTAMP() | TIMESTAMP(expr) | แสดงวันเวลาตาม format |
| TIMESTAMPADD() | TIMESTAMPADD (unit,interval,datetime_expr) | เพิ่มจำนวนเวลา |
| TIMESTAMPDIFF() | TIMESTAMPDIFF (unit,datetime_expr1,datetime_expr2) | คำนวณส่วนต่างของวันเวลา |
| TO_DAYS() | TO_DAYS(date) | แสดงจำนวนวัน ตั้งแต่ปีที่ 0 |
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(date) | แปลง epoch date หรือ UNIX timestamp |
| UTC_DATE() | UTC_DATE() | แสดงวันที่ปัจจุบัน UTC |
| UTC_TIME() | UTC_TIME() | แสดงเวลาปัจจุบัน UTC |
| UTC_TIMESTAMP() | UTC_TIMESTAMP() | แสดงวันเวลาปัจจุบัน UTC |
| WEEK() | WEEK(date[,mode]) | แสดงเลขของสัปดาห์ในปี (1-53) |
| WEEKDAY() | WEEKDAY(date) | แสดง weekday 0-6 (0 = วันจันทร์) |
| WEEKOFYEAR() | WEEKOFYEAR(date) | แสดงเลขของสัปดาห์ในปี (1-53) |
| YEAR() | YEAR(date) | แสดงข้อมูลปี |
| YEARWEEK() | YEARWEEK(date) | แสดงข้อมูลปีและสัปดาห์ |
ตัวอย่าง DATE Function
ADDDATE
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days), DATE_ADD(expr,days)
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1998-02-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| ADDDATE('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1998-02-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
สำหรับ MySQL argument ที่ 2 ถ้าไม่กำหนดหน่วยจะเป็นวัน
mysql> SELECT ADDDATE('1998-01-02', 31);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1998-02-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
ADDTIME
ADDTIME(expr1,expr2)
mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59.999999','1 1:1:1.000002') |
+---------------------------------------------------------+
| 1998-01-02 01:01:01.000001 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
CONVERT_TZ
CONVERT_TZ(dt,from_tz,to_tz)
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') |
+---------------------------------------------------------+
| 2004-01-01 13:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00') |
+---------------------------------------------------------+
| 2004-01-01 22:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
CURDATE
CURDATE(), CURRENT_DATE()
mysql> SELECT CURDATE();
+---------------------------------------------------------+
| CURDATE() |
+---------------------------------------------------------+
| 1997-12-15 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CURDATE() + 0;
+---------------------------------------------------------+
| CURDATE() + 0 |
+---------------------------------------------------------+
| 19971215 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
CURTIME
CURTIME(), CURRENT_TIME()
mysql> SELECT CURTIME();
+---------------------------------------------------------+
| CURTIME() |
+---------------------------------------------------------+
| 23:50:26 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CURTIME() + 0;
+---------------------------------------------------------+
| CURTIME() + 0 |
+---------------------------------------------------------+
| 235026 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DATE
DATE(expr)
mysql> SELECT DATE('2003-12-31 01:02:03');
+---------------------------------------------------------+
| DATE('2003-12-31 01:02:03') |
+---------------------------------------------------------+
| 2003-12-31 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DATEDIFF
DATEDIFF(expr1,expr2)
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
+---------------------------------------------------------+
| DATEDIFF('1997-12-31 23:59:59','1997-12-30') |
+---------------------------------------------------------+
| 1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DATE_ADD
DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
| Unit | Expected exprFormat |
| MICROSECOND | MICROSECONDS |
| SECOND | SECONDS |
| MINUTE | MINUTES |
| HOUR | HOURS |
| DAY | DAYS |
| WEEK | WEEKS |
| MONTH | MONTHS |
| QUARTER | QUARTERS |
| YEAR | YEARS |
| SECOND_MICROSECOND | ‘SECONDS.MICROSECONDS’ |
| MINUTE_MICROSECOND | ‘MINUTES.MICROSECONDS’ |
| MINUTE_SECOND | ‘MINUTES:SECONDS’ |
| HOUR_MICROSECOND | ‘HOURS.MICROSECONDS’ |
| HOUR_SECOND | ‘HOURS:MINUTES:SECONDS’ |
| HOUR_MINUTE | ‘HOURS:MINUTES’ |
| DAY_MICROSECOND | ‘DAYS.MICROSECONDS’ |
| DAY_SECOND | ‘DAYS HOURS:MINUTES:SECONDS’ |
| DAY_MINUTE | ‘DAYS HOURS:MINUTES’ |
| DAY_HOUR | ‘DAYS HOURS’ |
| YEAR_MONTH | ‘YEARS-MONTHS’ |
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59', INTERVAL... |
+---------------------------------------------------------+
| 1998-01-01 00:01:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
+---------------------------------------------------------+
| DATE_ADD('1999-01-01', INTERVAL 1 HOUR) |
+---------------------------------------------------------+
| 1999-01-01 01:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DATE_FORMAT
DATE_FORMAT(date,format)
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') |
+---------------------------------------------------------+
| Saturday October 1997 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00'
-> '%H %k %I %r %T %S %w');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00....... |
+---------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DAY
DAY(date), DAYOFMONTH(date)
mysql> SELECT DAYOFMONTH('1998-02-03');
+---------------------------------------------------------+
| DAYOFMONTH('1998-02-03') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DAYNAME
DAYNAME(date)
mysql> SELECT DAYNAME('1998-02-05');
+---------------------------------------------------------+
| DAYNAME('1998-02-05') |
+---------------------------------------------------------+
| Thursday |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DAYOFWEEK
DAYOFWEEK(date)
mysql> SELECT DAYOFWEEK('1998-02-03');
+---------------------------------------------------------+
|DAYOFWEEK('1998-02-03') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DAYOFYEAR
DAYOFYEAR(date)
mysql> SELECT DAYOFYEAR('1998-02-03');
+---------------------------------------------------------+
| DAYOFYEAR('1998-02-03') |
+---------------------------------------------------------+
| 34 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
EXTRACT
EXTRACT(unit FROM date)
mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
+---------------------------------------------------------+
| EXTRACT(YEAR FROM '1999-07-02') |
+---------------------------------------------------------+
| 1999 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
+---------------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03') |
+---------------------------------------------------------+
| 199907 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
FROM_DAYS
FROM_DAYS(N)
mysql> SELECT FROM_DAYS(729669);
+---------------------------------------------------------+
| FROM_DAYS(729669) |
+---------------------------------------------------------+
| 1997-10-07 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
FROM_UNIXTIME
FROM_UNIXTIME(unix_timestamp)
mysql> SELECT FROM_UNIXTIME(875996580);
+---------------------------------------------------------+
| FROM_UNIXTIME(875996580) |
+---------------------------------------------------------+
| 1997-10-04 22:23:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
HOUR
HOUR(time)
mysql> SELECT HOUR('10:05:03');
+---------------------------------------------------------+
| HOUR('10:05:03') |
+---------------------------------------------------------+
| 10 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
LAST_DAY
LAST_DAY(date)
mysql> SELECT LAST_DAY('2003-02-05');
+---------------------------------------------------------+
| LAST_DAY('2003-02-05') |
+---------------------------------------------------------+
| 2003-02-28 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
MAKEDATE
MAKEDATE(year,dayofyear)
mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
+---------------------------------------------------------+
| MAKEDATE(2001,31), MAKEDATE(2001,32) |
+---------------------------------------------------------+
| '2001-01-31', '2001-02-01' |
+---------------------------------------------------------+
1 row in set (0.00 sec)
MAKETIME
MAKETIME(hour,minute,second)
mysql> SELECT MAKETIME(12,15,30);
+---------------------------------------------------------+
| MAKETIME(12,15,30) |
+---------------------------------------------------------+
| '12:15:30' |
+---------------------------------------------------------+
1 row in set (0.00 sec)
MICROSECOND
MICROSECOND(expr)
mysql> SELECT MICROSECOND('12:00:00.123456');
+---------------------------------------------------------+
| MICROSECOND('12:00:00.123456') |
+---------------------------------------------------------+
| 123456 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
MINUTE
MINUTE(time)
mysql> SELECT MINUTE('98-02-03 10:05:03');
+---------------------------------------------------------+
| MINUTE('98-02-03 10:05:03') |
+---------------------------------------------------------+
| 5 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
MONTH
MONTH(date)
mysql> SELECT MONTH('1998-02-03')
+---------------------------------------------------------+
| MONTH('1998-02-03') |
+---------------------------------------------------------+
| 2 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
MONTHNAME
MONTHNAME(date)
mysql> SELECT MONTHNAME('1998-02-05');
+---------------------------------------------------------+
| MONTHNAME('1998-02-05') |
+---------------------------------------------------------+
| February |
+---------------------------------------------------------+
1 row in set (0.00 sec)
NOW
NOW(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP()
mysql> SELECT NOW();
+---------------------------------------------------------+
| NOW() |
+---------------------------------------------------------+
| 1997-12-15 23:50:26 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
PERIOD_ADD
PERIOD_ADD(P,N)
mysql> SELECT PERIOD_ADD(9801,2);
+---------------------------------------------------------+
| PERIOD_ADD(9801,2) |
+---------------------------------------------------------+
| 199803 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
PERIOD_DIFF
PERIOD_DIFF(P1,P2)
mysql> SELECT PERIOD_DIFF(9802,199703);
+---------------------------------------------------------+
| PERIOD_DIFF(9802,199703) |
+---------------------------------------------------------+
| 11 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
QUARTER
QUARTER(date)
mysql> SELECT QUARTER('98-04-01');
+---------------------------------------------------------+
| QUARTER('98-04-01') |
+---------------------------------------------------------+
| 2 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SECOND
SECOND(time)
mysql> SELECT SECOND('10:05:03'); +---------------------------------------------------------+ | SECOND('10:05:03') | +---------------------------------------------------------+ | 3 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
SEC_TO_TIME
SEC_TO_TIME(seconds)
mysql> SELECT SEC_TO_TIME(2378);
+---------------------------------------------------------+
| SEC_TO_TIME(2378) |
+---------------------------------------------------------+
| 00:39:38 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
STR_TO_DATE
STR_TO_DATE(str,format)
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
+---------------------------------------------------------+
| STR_TO_DATE('04/31/2004', '%m/%d/%Y') |
+---------------------------------------------------------+
| 2004-04-31 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SUBDATE
SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1997-12-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| SUBDATE('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1997-12-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SUBTIME
SUBTIME(expr1,expr2)
mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999',
-> '1 1:1:1.000002');
+---------------------------------------------------------+
| SUBTIME('1997-12-31 23:59:59.999999'... |
+---------------------------------------------------------+
| 1997-12-30 22:58:58.999997 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SYSDATE
SYSDATE()
mysql> SELECT SYSDATE();
+---------------------------------------------------------+
| SYSDATE() |
+---------------------------------------------------------+
| 2006-04-12 13:47:44 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIME
TIME(expr)
mysql> SELECT TIME('2003-12-31 01:02:03');
+---------------------------------------------------------+
| TIME('2003-12-31 01:02:03') |
+---------------------------------------------------------+
| 01:02:03 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIMEDIFF
TIMEDIFF(expr1,expr2)
mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',
-> '1997-12-30 01:01:01.000002');
+---------------------------------------------------------+
| TIMEDIFF('1997-12-31 23:59:59.000001'..... |
+---------------------------------------------------------+
| 46:58:57.999999 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIMESTAMP
TIMESTAMP(expr), TIMESTAMP(expr1,expr2)
mysql> SELECT TIMESTAMP('2003-12-31');
+---------------------------------------------------------+
| TIMESTAMP('2003-12-31') |
+---------------------------------------------------------+
| 2003-12-31 00:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIMESTAMPADD
TIMESTAMPADD(unit,interval,datetime_expr)
mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
+---------------------------------------------------------+
| TIMESTAMPADD(MINUTE,1,'2003-01-02') |
+---------------------------------------------------------+
| 2003-01-02 00:01:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIMESTAMPDIFF
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+---------------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIME_FORMAT
TIME_FORMAT(time,format)
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
+---------------------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l') |
+---------------------------------------------------------+
| 100 100 04 04 4 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIME_TO_SEC
TIME_TO_SEC(time)
mysql> SELECT TIME_TO_SEC('22:23:00');
+---------------------------------------------------------+
| TIME_TO_SEC('22:23:00') |
+---------------------------------------------------------+
| 80580 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TO_DAYS
TO_DAYS(date)
mysql> SELECT TO_DAYS(950501);
+---------------------------------------------------------+
| TO_DAYS(950501) |
+---------------------------------------------------------+
| 728779 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
UNIX_TIMESTAMP
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
mysql> SELECT UNIX_TIMESTAMP();
+---------------------------------------------------------+
| UNIX_TIMESTAMP() |
+---------------------------------------------------------+
| 882226357 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
+---------------------------------------------------------+
| UNIX_TIMESTAMP('1997-10-04 22:23:00') |
+---------------------------------------------------------+
| 875996580 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
UTC_DATE
UTC_DATE()
mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
+---------------------------------------------------------+
| UTC_DATE(), UTC_DATE() + 0 |
+---------------------------------------------------------+
| 2003-08-14, 20030814 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
UTC_TIME
UTC_TIME()
mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
+---------------------------------------------------------+
| UTC_TIME(), UTC_TIME() + 0 |
+---------------------------------------------------------+
| 18:07:53, 180753 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
UTC_TIMESTAMP
UTC_TIMESTAMP()
mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
+---------------------------------------------------------+
| UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0 |
+---------------------------------------------------------+
| 2003-08-14 18:08:04, 20030814180804 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
WEEK
WEEK(date[,mode])
| Mode | First Day of week | Range |
| 0 | Sunday | 0-53 |
| 1 | Monday | 0-53 |
| 2 | Sunday | 1-53 |
| 3 | Monday | 1-53 |
| 4 | Sunday | 0-53 |
| 5 | Monday | 0-53 |
| 6 | Sunday | 1-53 |
| 7 | Monday | 1-53 |
mysql> SELECT WEEK('1998-02-20');
+---------------------------------------------------------+
| WEEK('1998-02-20') |
+---------------------------------------------------------+
| 7 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
WEEKDAY
WEEKDAY(date)
mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
+---------------------------------------------------------+
| WEEKDAY('1998-02-03 22:23:00') |
+---------------------------------------------------------+
| 1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
WEEKOFYEAR
WEEKOFYEAR(date)
mysql> SELECT WEEKOFYEAR('1998-02-20');
+---------------------------------------------------------+
| WEEKOFYEAR('1998-02-20') |
+---------------------------------------------------------+
| 8 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
YEAR
YEAR(date)
mysql> SELECT YEAR('98-02-03');
+---------------------------------------------------------+
| YEAR('98-02-03') |
+---------------------------------------------------------+
| 1998 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
YEARWEEK
YEARWEEK(date), YEARWEEK(date,mode)
mysql> SELECT YEARWEEK('1987-01-01');
+---------------------------------------------------------+
| YEAR('98-02-03')YEARWEEK('1987-01-01') |
+---------------------------------------------------------+
| 198653 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Reference:
รวมคำสั่ง SQL Command พื้นฐานเบื้องต้น
Author: Suphakit Annoppornchai
Credit: https://saixiii.com, https://www.tutorialspoint.com
